﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Maticsoft.DBUtility;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    /// <summary>
    /// Paper数据类
    /// </summary>
    public class Paper
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public Paper()
        { }

        /// <summary>
        /// 某个调查类别是否存在该记录
        /// </summary>
        /// <param name="paperClsNo"></param>
        /// <param name="entityNo"></param>
        /// <returns></returns>
        public bool Exists(string paperClsNo, string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_Paper");
            strSql.Append(" where FPaperClsNo=@FPaperClsNo and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FPaperClsNo", SqlDbType.VarChar,30),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,15)};
            parameters[0].Value = paperClsNo;
            parameters[1].Value = entityNo;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-7-25</date>
        /// <param name="fPaperId">主键</param>
        /// <returns>一个对象实体</returns>
        public Model.Paper GetModel(decimal fPaperId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FPaperClsNo,FTitle,FContent,FIsRecordName,FBeginDate,FEndDate,FResultIsOpen,FPaperId,FEntityNo,FPubDate,FPubDeptNo,FPubEmpNo,FPubIp,FPaperType,FIsVoteIPControl,FIsLookIPControl from t_Paper ");
            strSql.Append(" where FPaperId=@FPaperId");
            SqlParameter[] parameters = {
					new SqlParameter("@FPaperId", SqlDbType.Decimal)
			};
            parameters[0].Value = fPaperId;

            Model.Paper model = new Model.Paper();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["FPaperClsNo"] != null && ds.Tables[0].Rows[0]["FPaperClsNo"].ToString() != "")
                {
                    model.FPaperClsNo = ds.Tables[0].Rows[0]["FPaperClsNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FTitle"] != null && ds.Tables[0].Rows[0]["FTitle"].ToString() != "")
                {
                    model.FTitle = ds.Tables[0].Rows[0]["FTitle"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FContent"] != null && ds.Tables[0].Rows[0]["FContent"].ToString() != "")
                {
                    model.FContent = ds.Tables[0].Rows[0]["FContent"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FIsRecordName"] != null && ds.Tables[0].Rows[0]["FIsRecordName"].ToString() != "")
                {
                    model.FIsRecordName = ds.Tables[0].Rows[0]["FIsRecordName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FBeginDate"] != null && ds.Tables[0].Rows[0]["FBeginDate"].ToString() != "")
                {
                    model.FBeginDate = DateTime.Parse(ds.Tables[0].Rows[0]["FBeginDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FEndDate"] != null && ds.Tables[0].Rows[0]["FEndDate"].ToString() != "")
                {
                    model.FEndDate = DateTime.Parse(ds.Tables[0].Rows[0]["FEndDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FResultIsOpen"] != null && ds.Tables[0].Rows[0]["FResultIsOpen"].ToString() != "")
                {
                    model.FResultIsOpen = ds.Tables[0].Rows[0]["FResultIsOpen"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FPaperId"] != null && ds.Tables[0].Rows[0]["FPaperId"].ToString() != "")
                {
                    model.FPaperId = decimal.Parse(ds.Tables[0].Rows[0]["FPaperId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FEntityNo"] != null && ds.Tables[0].Rows[0]["FEntityNo"].ToString() != "")
                {
                    model.FEntityNo = ds.Tables[0].Rows[0]["FEntityNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FPubDate"] != null && ds.Tables[0].Rows[0]["FPubDate"].ToString() != "")
                {
                    model.FPubDate = DateTime.Parse(ds.Tables[0].Rows[0]["FPubDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FPubDeptNo"] != null && ds.Tables[0].Rows[0]["FPubDeptNo"].ToString() != "")
                {
                    model.FPubDeptNo = ds.Tables[0].Rows[0]["FPubDeptNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FPubEmpNo"] != null && ds.Tables[0].Rows[0]["FPubEmpNo"].ToString() != "")
                {
                    model.FPubEmpNo = ds.Tables[0].Rows[0]["FPubEmpNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FPubIp"] != null && ds.Tables[0].Rows[0]["FPubIp"].ToString() != "")
                {
                    model.FPubIp = ds.Tables[0].Rows[0]["FPubIp"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FPaperType"] != null && ds.Tables[0].Rows[0]["FPaperType"].ToString() != "")
                {
                    model.FPaperType = ds.Tables[0].Rows[0]["FPaperType"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FIsVoteIPControl"] != null && ds.Tables[0].Rows[0]["FIsVoteIPControl"].ToString() != "")
                {
                    model.FIsVoteIPControl = ds.Tables[0].Rows[0]["FIsVoteIPControl"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FIsLookIPControl"] != null && ds.Tables[0].Rows[0]["FIsLookIPControl"].ToString() != "")
                {
                    model.FIsLookIPControl = ds.Tables[0].Rows[0]["FIsLookIPControl"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 获得数据列表
        /// <creator>chenly</creator>
        /// <date>2013-7-25</date>
        /// </summary>
        /// <param name="strWhere">查询条件</param>
        /// <returns>DataSet表</returns>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FPaperClsNo,FTitle,FContent,FIsRecordName,FBeginDate,FEndDate,FResultIsOpen,FPaperId,FEntityNo,FPubDate,FPubDeptNo,FPubEmpNo,FPubIp,FPaperType ");
            strSql.Append(" FROM t_Paper ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        /// 增加主题和主题权限 事务模式
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-7-25</date>
        /// <param name="paperModel">主题的一个Model对象</param>
        /// <param name="readAuthList">题目管理的集合</param>
        /// <param name="paperIPList">IP集合</param>
        /// <returns>返回</returns>
        public decimal AddPaperAndReadAuth(Model.Paper paperModel, List<Model.PaperReadAuth> readAuthList, List<Model.PaperIP> paperIPList)
        {
            StringBuilder strSql = new StringBuilder();
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();

            //增加主题数据
            strSql.Append("insert into t_Paper(");
            strSql.Append("FPaperClsNo,FTitle,FContent,FIsRecordName,FBeginDate,FEndDate,FResultIsOpen,FEntityNo,FPubDate,FPubDeptNo,FPubEmpNo,FPubIp,FPaperType,FIsVoteIPControl,FIsLookIPControl)");
            strSql.Append(" values (");
            strSql.Append("@FPaperClsNo,@FTitle,@FContent,@FIsRecordName,@FBeginDate,@FEndDate,@FResultIsOpen,");
            strSql.Append("@FEntityNo,@FPubDate,@FPubDeptNo,@FPubEmpNo,@FPubIp,@FPaperType,@FIsVoteIPControl,@FIsLookIPControl)");
            strSql.Append(";select @returnID=IDENT_CURRENT('t_Paper')");
            SqlParameter[] parameters = {
					new SqlParameter("@FPaperClsNo", SqlDbType.VarChar,30),
					new SqlParameter("@FTitle", SqlDbType.NVarChar,255),
					new SqlParameter("@FContent", SqlDbType.NVarChar),
					new SqlParameter("@FIsRecordName", SqlDbType.Char,1),
					new SqlParameter("@FBeginDate", SqlDbType.DateTime),
					new SqlParameter("@FEndDate", SqlDbType.DateTime),
					new SqlParameter("@FResultIsOpen", SqlDbType.Char,1),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FPubDate", SqlDbType.DateTime),
					new SqlParameter("@FPubDeptNo", SqlDbType.VarChar,30),
					new SqlParameter("@FPubEmpNo", SqlDbType.VarChar,30),
					new SqlParameter("@FPubIp", SqlDbType.VarChar,15),
					new SqlParameter("@FPaperType", SqlDbType.Char,1),
                    new SqlParameter("@FIsVoteIPControl", SqlDbType.NVarChar,1),
					new SqlParameter("@FIsLookIPControl", SqlDbType.NVarChar,1),
                    new SqlParameter("@returnID",SqlDbType.Decimal)};
            parameters[0].Value = paperModel.FPaperClsNo;
            parameters[1].Value = paperModel.FTitle;
            parameters[2].Value = paperModel.FContent;
            parameters[3].Value = paperModel.FIsRecordName;
            parameters[4].Value = paperModel.FBeginDate;
            parameters[5].Value = paperModel.FEndDate;
            parameters[6].Value = paperModel.FResultIsOpen;
            parameters[7].Value = paperModel.FEntityNo;
            parameters[8].Value = paperModel.FPubDate;
            parameters[9].Value = paperModel.FPubDeptNo;
            parameters[10].Value = paperModel.FPubEmpNo;
            parameters[11].Value = paperModel.FPubIp;
            parameters[12].Value = paperModel.FPaperType;
            parameters[13].Value = paperModel.FIsVoteIPControl;
            parameters[14].Value = paperModel.FIsLookIPControl;
            parameters[15].Direction = ParameterDirection.Output;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            //增加权限数据

            if (readAuthList.Count > 0)
            {
                strSql = new StringBuilder();
                strSql.Append("insert into t_PaperReadAuth(");
                strSql.Append("FPaperId,FSourceNo,FProp,FPronoun,FEntityNo)");
                strSql.Append(" values (");
                strSql.Append("@FPaperId,@FSourceNo,@FProp,@FPronoun,@FEntityNo)");

                string strSqls = strSql.ToString();

                foreach (Model.PaperReadAuth model in readAuthList)
                {
                    cmds = new CommandInfo();
                    SqlParameter[] parameters1 = {
					    new SqlParameter("@FPaperId", SqlDbType.Decimal),
					    new SqlParameter("@FSourceNo", SqlDbType.NVarChar,25),
					    new SqlParameter("@FProp", SqlDbType.Char,1),
					    new SqlParameter("@FPronoun", SqlDbType.NVarChar,25),
					    new SqlParameter("@FEntityNo", SqlDbType.NVarChar,15)};
                    parameters1[0].Direction = ParameterDirection.InputOutput;
                    parameters1[1].Value = model.FSourceNo;
                    parameters1[2].Value = model.FProp;
                    parameters1[3].Value = model.FPronoun;
                    parameters1[4].Value = model.FEntityNo;

                    cmds.CommandText = strSqls;
                    cmds.Parameters = parameters1;
                    cmdList.Add(cmds);
                }
            }
            ///IP组
            if (paperIPList.Count > 0)
            {
                strSql = new StringBuilder();
                strSql.Append("insert into t_PaperIP(");
                strSql.Append("FProp,FPaperId,FIPGroupID)");
                strSql.Append(" values (");
                strSql.Append("@FProp,@FPaperId,@FIPGroupID)");

                string strSqls = strSql.ToString();

                foreach (Model.PaperIP model in paperIPList) {
                    cmds = new CommandInfo();
                    SqlParameter[] parameters2 = {
					    new SqlParameter("@FPaperId", SqlDbType.Decimal,9),
					    new SqlParameter("@FProp", SqlDbType.NVarChar,20),
					    new SqlParameter("@FIPGroupID", SqlDbType.Int,4)};
                    parameters2[0].Direction = ParameterDirection.InputOutput;
                    parameters2[1].Value = model.FProp;
                    parameters2[2].Value = model.FIPGroupID;

                    cmds.CommandText = strSqls;
                    cmds.Parameters = parameters2;
                    cmdList.Add(cmds);
                }

            }

            //执行存储过程，生成临时表数据，以便查询时调用 
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("EXEC sp_genPaperAuthorityDW @EntityNo");
            SqlParameter[] parameters4 = {
					new SqlParameter("@EntityNo", SqlDbType.NVarChar,12)};
            parameters4[0].Value = paperModel.FEntityNo;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters4;
            cmdList.Add(cmds);

            DbHelperSQL.ExecuteSqlTranWithString(cmdList);
            return decimal.Parse(parameters[15].Value.ToString());

        }


        /// <summary>
        /// 修改主题和查看权限 事务模式
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-7-25</date>
        /// <param name="paperModel">主题的一个Model对象</param>
        /// <param name="readAuthList">题目管理的集合</param>
        /// <param name="paperIPList">IP集合</param> 
        public void UpdPaperAndReadAuth(Model.Paper paperModel, List<Model.PaperReadAuth> readAuthList, List<Model.PaperIP> paperIPList)
        {
            StringBuilder strSql = new StringBuilder();
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();

            //修改主题数据
            strSql.Append("update t_Paper set ");
            strSql.Append("FPaperClsNo=@FPaperClsNo,");
            strSql.Append("FTitle=@FTitle,");
            strSql.Append("FContent=@FContent,");
            strSql.Append("FIsRecordName=@FIsRecordName,");
            strSql.Append("FBeginDate=@FBeginDate,");
            strSql.Append("FEndDate=@FEndDate,");
            strSql.Append("FResultIsOpen=@FResultIsOpen,");
            strSql.Append("FEntityNo=@FEntityNo,");
            strSql.Append("FPubDate=@FPubDate,");
            strSql.Append("FPubDeptNo=@FPubDeptNo,");
            strSql.Append("FPubEmpNo=@FPubEmpNo,");
            strSql.Append("FPubIp=@FPubIp,");
            strSql.Append("FPaperType=@FPaperType,");
            strSql.Append("FIsVoteIPControl=@FIsVoteIPControl,");
            strSql.Append("FIsLookIPControl=@FIsLookIPControl");
            strSql.Append(" where FPaperId=@FPaperId");
            SqlParameter[] parameters = {
					new SqlParameter("@FPaperClsNo", SqlDbType.VarChar,30),
					new SqlParameter("@FTitle", SqlDbType.NVarChar,255),
					new SqlParameter("@FContent", SqlDbType.NVarChar),
					new SqlParameter("@FIsRecordName", SqlDbType.Char,1),
					new SqlParameter("@FBeginDate", SqlDbType.DateTime),
					new SqlParameter("@FEndDate", SqlDbType.DateTime),
					new SqlParameter("@FResultIsOpen", SqlDbType.Char,1),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FPubDate", SqlDbType.DateTime),
					new SqlParameter("@FPubDeptNo", SqlDbType.VarChar,30),
					new SqlParameter("@FPubEmpNo", SqlDbType.VarChar,30),
					new SqlParameter("@FPubIp", SqlDbType.VarChar,15),
					new SqlParameter("@FPaperType", SqlDbType.Char,1),
					new SqlParameter("@FPaperId", SqlDbType.Decimal),
                    new SqlParameter("@FIsVoteIPControl", SqlDbType.NVarChar,1),
					new SqlParameter("@FIsLookIPControl", SqlDbType.NVarChar,1)};
            parameters[0].Value = paperModel.FPaperClsNo;
            parameters[1].Value = paperModel.FTitle;
            parameters[2].Value = paperModel.FContent;
            parameters[3].Value = paperModel.FIsRecordName;
            parameters[4].Value = paperModel.FBeginDate;
            parameters[5].Value = paperModel.FEndDate;
            parameters[6].Value = paperModel.FResultIsOpen;
            parameters[7].Value = paperModel.FEntityNo;
            parameters[8].Value = paperModel.FPubDate;
            parameters[9].Value = paperModel.FPubDeptNo;
            parameters[10].Value = paperModel.FPubEmpNo;
            parameters[11].Value = paperModel.FPubIp;
            parameters[12].Value = paperModel.FPaperType;
            parameters[13].Value = paperModel.FPaperId;
            parameters[14].Value = paperModel.FIsVoteIPControl;
            parameters[15].Value = paperModel.FIsLookIPControl;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);


            //删除除部门外的所有权限
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("delete from t_PaperReadAuth where FPaperId=@FPaperId");
            SqlParameter[] parameters0 = { 
                    new SqlParameter("@FPaperId", SqlDbType.Decimal) };
            parameters0[0].Value = paperModel.FPaperId;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters0;
            cmdList.Add(cmds);

            //增加权限数据
            if (readAuthList.Count > 0)
            {
                strSql = new StringBuilder();
                strSql.Append("insert into t_PaperReadAuth(");
                strSql.Append("FPaperId,FSourceNo,FProp,FPronoun,FEntityNo)");
                strSql.Append(" values (");
                strSql.Append("@FPaperId,@FSourceNo,@FProp,@FPronoun,@FEntityNo)");

                string strSqls = strSql.ToString();

                foreach (Model.PaperReadAuth model in readAuthList)
                {
                    cmds = new CommandInfo();
                    SqlParameter[] parameters1 = {
					    new SqlParameter("@FPaperId", SqlDbType.Decimal),
					    new SqlParameter("@FSourceNo", SqlDbType.NVarChar,25),
					    new SqlParameter("@FProp", SqlDbType.Char,1),
					    new SqlParameter("@FPronoun", SqlDbType.NVarChar,25),
					    new SqlParameter("@FEntityNo", SqlDbType.NVarChar,15)};
                    parameters1[0].Value = paperModel.FPaperId;
                    parameters1[1].Value = model.FSourceNo;
                    parameters1[2].Value = model.FProp;
                    parameters1[3].Value = model.FPronoun;
                    parameters1[4].Value = model.FEntityNo;

                    cmds.CommandText = strSqls;
                    cmds.Parameters = parameters1;
                    cmdList.Add(cmds);
                }
            }

            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("delete from t_PaperIP where FPaperId=@FPaperId");
            SqlParameter[] parameters2 = { 
                    new SqlParameter("@FPaperId", SqlDbType.Decimal) };
            parameters2[0].Value = paperModel.FPaperId;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters2;
            cmdList.Add(cmds);


            ///IP组
            if (paperIPList.Count > 0)
            {
                strSql = new StringBuilder();
                strSql.Append("insert into t_PaperIP(");
                strSql.Append("FProp,FPaperId,FIPGroupID)");
                strSql.Append(" values (");
                strSql.Append("@FProp,@FPaperId,@FIPGroupID)");

                string strSqls = strSql.ToString();

                foreach (Model.PaperIP model in paperIPList)
                {
                    cmds = new CommandInfo();
                    SqlParameter[] parameters3 = {
					    new SqlParameter("@FPaperId", SqlDbType.Decimal),
					    new SqlParameter("@FProp", SqlDbType.NVarChar,20),
					    new SqlParameter("@FIPGroupID", SqlDbType.Int,4)};
                    parameters3[0].Value = paperModel.FPaperId;
                    parameters3[1].Value = model.FProp;
                    parameters3[2].Value = model.FIPGroupID;

                    cmds.CommandText = strSqls;
                    cmds.Parameters = parameters3;
                    cmdList.Add(cmds);
                }

            }

            //执行存储过程，生成临时表数据，以便查询时调用 
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("EXEC sp_genPaperAuthorityDW @EntityNo");
            SqlParameter[] parameters4 = {
					new SqlParameter("@EntityNo", SqlDbType.NVarChar,12)};
            parameters4[0].Value = paperModel.FEntityNo;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters4;
            cmdList.Add(cmds);

            DbHelperSQL.ExecuteSqlTranWithString(cmdList);


        }

        /// <summary>
        /// 删除主题和主题权限 事务模式
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-7-25</date>
        /// <param name="paperID">管理题目的编号</param>
        public void DelPapetAndReadAuth(decimal paperID)
        {
            StringBuilder strSql = new StringBuilder();
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();


            SqlParameter[] parameters = {
					new SqlParameter("@FPaperId", SqlDbType.Decimal)};

            parameters[0].Value = paperID;


            //删除主题权限            
            strSql.Append("delete from t_PaperReadAuth ");
            strSql.Append(" where FPaperId=@FPaperId");

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            //删除主题
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("delete from t_Paper ");
            strSql.Append(" where FPaperId=@FPaperId");


            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);



            DbHelperSQL.ExecuteSqlTranWithString(cmdList);


        }


    }
}
